package com.ezjie.ielts.db;

import java.util.ArrayList;
import java.util.List;
import java.util.Set;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.ezjie.ielts.core.helper.AppDataBaseHelper;
import com.ezjie.ielts.model.AudioFile;
import com.ezjie.ielts.model.ExamListDetail;

public class ExamListInfoDB {
	private static AppDataBaseHelper dbHelper;
	private SQLiteDatabase db;
	private GroupInfoDB groupInfo;

	public ExamListInfoDB(Context context) {
		dbHelper = AppDataBaseHelper.getInstance(context);
		groupInfo = new GroupInfoDB(context);
	}

	// 查找真题
	public synchronized List<ExamListDetail> query(int type) {
		db = dbHelper.getReadableDatabase();

		List<ExamListDetail> lists = new ArrayList<ExamListDetail>();

		String sql = "select * from " + AppDataBaseHelper.EXAMINFO;
		Cursor cursor = db.rawQuery(sql, null);
		while (cursor.moveToNext()) {
			ExamListDetail info = new ExamListDetail();
			info.book = cursor.getString(1);
			info.task = cursor.getString(2);
			info.category_id = cursor.getString(3);
			info.title = cursor.getString(4);
			info.title_en = cursor.getString(5);
			info.android_img = cursor.getString(6);
			info.ios_img = cursor.getString(7);
			info.type = cursor.getInt(8);
			info.classify_id = cursor.getString(9);
			info.last_scored = cursor.getString(10);
			info.class_type = cursor.getString(11);
			if(info.type == type){
				lists.add(info);
			}
		}
		cursor.close();
		db.close();

		return lists;
	}

	// 查找真题
	public synchronized List<ExamListDetail> queryListener() {
		db = dbHelper.getReadableDatabase();

		StringBuilder sb = new StringBuilder();
		Set<String> set1 = groupInfo.queryListener(db);
		int i = 0;
		for(String str : set1){
			sb.append("'");
			sb.append(str);
			sb.append("'");
			if(i < set1.size() -1){
				sb.append(",");
				i++;
			}
		}
		List<ExamListDetail> lists = new ArrayList<ExamListDetail>();
		String sql = "select * from " + AppDataBaseHelper.EXAMINFO + " where " + "category_id in ( " + sb + " )";
		Cursor cursor = db.rawQuery(sql, null);
		while (cursor.moveToNext()) {
			ExamListDetail info = new ExamListDetail();
			info.book = cursor.getString(1);
			info.task = cursor.getString(2);
			info.category_id = cursor.getString(3);
			info.title = cursor.getString(4);
			info.title_en = cursor.getString(5);
			info.android_img = cursor.getString(6);
			info.ios_img = cursor.getString(7);
			info.type = cursor.getInt(8);
			info.classify_id = cursor.getString(9);
			info.last_scored = cursor.getString(10);
			info.class_type = cursor.getString(11);
			lists.add(info);
		}
		cursor.close();
		db.close();

		return lists;
	}

	// 删除真题
	public synchronized void delete() {
		db = dbHelper.getWritableDatabase();

		String sql = "delete from " + AppDataBaseHelper.EXAMINFO;
		db.execSQL(sql);
		db.close();
	}

	// 更新真题
	public synchronized void update() {
		db = dbHelper.getWritableDatabase();

		String sql = "update " + AppDataBaseHelper.EXAMINFO + " set last_scored= ? ";
		db.execSQL(sql, new Object[] { 1 });
		db.close();
	}

	/**
	 * 插入真题
	 * 
	 * @param category
	 */
	public synchronized void insert(ExamListDetail examInfo) {
		db = dbHelper.getWritableDatabase();

		// db.beginTransaction();
		String sql = "insert into " + AppDataBaseHelper.EXAMINFO
				+ " (book,task,category_id,title,title_en,android_img,ios_img,type,classify_id,last_scored,class_type) "
				+ "values(?,?,?,?,?,?,?,?,?,?,?)";
		db.execSQL(sql, new Object[] { examInfo.book, examInfo.task, examInfo.category_id, examInfo.title,
				examInfo.title_en, examInfo.android_img, examInfo.ios_img, examInfo.type, examInfo.classify_id,
				examInfo.last_scored, examInfo.class_type });
		db.close();
	}

	/**
	 * 更新真题
	 * 
	 */
	public synchronized void replaceInto(List<ExamListDetail> examInfos) {
		db = dbHelper.getWritableDatabase();

		for (int i = 0; i < examInfos.size(); i++) {
			String sql = "replace into " + AppDataBaseHelper.EXAMINFO
					+ " (book,task,category_id,title,title_en,android_img,ios_img,type,classify_id,last_scored,class_type) "
					+ "values(?,?,?,?,?,?,?,?,?,?,?)";
			db.execSQL(sql, new Object[] { examInfos.get(i).book, examInfos.get(i).task, examInfos.get(i).category_id,
					examInfos.get(i).title, examInfos.get(i).title_en, examInfos.get(i).android_img,
					examInfos.get(i).ios_img, examInfos.get(i).type, examInfos.get(i).classify_id,
					examInfos.get(i).last_scored, examInfos.get(i).class_type });
		}

		db.close();
	}
}
